# coding=utf-8
import aiomysql
import asyncio
from datetime import datetime
from collections import OrderedDict

async def detail_by_problem(problem_id_list: list, sorting_type: str):
    '''
    :param problem_id_list: 问题ID列表
    :return:
    '''
    conn = None
    cursor = None
    try:
        conn = await aiomysql.connect(
            host='192.168.2.244',
            port=3306,
            user='root',
            password='linkAge@12345',
            charset='utf8mb4',
            db='stock_exchange'
        )

        detail_list = []
        cursor = await conn.cursor()
        for problem_id in problem_id_list:
            # print('id_list', id_list)

            await cursor.execute("SELECT problem_order, problem_title, project_id, file_name, datetime, round, problem_classification_v3 FROM problem WHERE problem_id = %s", (problem_id,))
            problem_dict = OrderedDict()
            problem_results = await cursor.fetchone()
            problem_dict[problem_results[0]] = problem_results[1]

            await cursor.execute("SELECT company_abbreviation FROM project WHERE project_id = %s", (problem_results[2],))
            title_dit = OrderedDict()
            project_results = await cursor.fetchone()
            title_dit['公司简称'] = project_results[0]
            title_dit['文件名称'] = problem_results[3]
            title_dit['日期'] = problem_results[4].strftime('%Y-%m-%d')
            title_dit['阶段'] = '已问询_上市委会议'
            title_dit['本阶段轮次'] = problem_results[5]
            title_dit['问题标签'] = problem_results[6]


            await cursor.execute("SELECT material_order, material_content FROM material WHERE problem_id = %s", (problem_id,))
            material_results = await cursor.fetchall()
            material_dict = OrderedDict()
            for material_ in material_results:
                material_dict[material_[0]] = material_[1]

            await cursor.execute("SELECT query_order, query_content FROM query WHERE problem_id = %s", (problem_id,))
            query_results = await cursor.fetchall()
            query_dict = OrderedDict()
            for query_ in query_results:
                query_dict[query_[0]] = query_[1]

            await cursor.execute("SELECT answer_order, answer_content FROM answer WHERE problem_id = %s", (problem_id,))
            answer_results = await cursor.fetchall()
            answer_dict = OrderedDict()
            for answer_ in answer_results:
                answer_dict[answer_[0]] = answer_[1]

            if len(query_results) != len(answer_results):
                print('问答不相等，跳过该材料。')
                continue
            detail_list.append(
                {'补充信息': title_dit, '问题': problem_dict, '材料': material_dict, '提问': query_dict, '回复': answer_dict})

        # 根据补充信息中的日期字段进行降序排列
        if sorting_type == '日期降序':
            sorted_detail_list = sorted(detail_list, key=lambda x: x['补充信息']['日期'], reverse=True)
        elif sorting_type == '日期升序':
            sorted_detail_list = sorted(detail_list, key=lambda x: x['补充信息']['日期'], reverse=False)
        elif sorting_type == '相似度降序':
            sorted_detail_list = detail_list
        else:
            print('请输入正确的排序方式sorting_type[日期降序、日期升序、相似度降序]')
            raise ValueError('请输入正确的排序方式sorting_type[日期降序、日期升序、相似度降序]')
        return sorted_detail_list

    except Exception as e:
        print(f"C6 error : {e}")
        return f"C6 error : {e}"

    finally:
        if cursor:
            await cursor.close()  # 使用 await 关键字
        if conn:
            conn.close()


if __name__ == '__main__':
    import asyncio
    from pprint import pprint

    # material_id_list = [[1, 2, 3, 4, 5, 6], [11, 21, 31, 41, 51, 61]]
    problem_id_list = [569, 538, 86, 17, 61, 27, 51]
    sorting_type = '相似度降序'
    detail_list = asyncio.run(detail_by_problem(problem_id_list, sorting_type))
    pprint(detail_list)

    # [{'材料': {'材料顺序(int)': '材料内容(str)'},
    #   '提问': {'提问顺序(int)': '提问内容(str)'},
    #   '回复': {'回复顺序（int）': '回复内容（str）'},
    #   }]
